﻿using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading.Tasks;

namespace SubstrateHelper.Data
{
    public static class EntityFrameworkCoreExtensions
    {
        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection)
        {
            var conn = facade.GetDbConnection();
            connection = conn;
            conn.Open();
            var cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            return cmd;
        }

        public static DataTable SqlQuery(this DatabaseFacade facade, string sql)
        {
            var command = CreateCommand(facade, sql, out DbConnection conn);
            var reader = command.ExecuteReader();
            
            var dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }

        public static T SqlQuertyScalar<T>(this DatabaseFacade facade, string sql)
        {
            var command = CreateCommand(facade, sql, out DbConnection conn);
            return (T)command.ExecuteScalar();
        }

        public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql) where T : class, new()
        {
            var dt = SqlQuery(facade, sql);
            return dt.ToList<T>();
        }

        public static List<T> ToList<T>(this DataTable dt) where T : class, new()
        {
            var propertyInfos = typeof(T).GetProperties();
            var list = new List<T>();
            foreach (DataRow row in dt.Rows)
            {
                var t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                list.Add(t);
            }
            return list;
        }

        /// <summary>
        /// DataTable转换为List
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="dt">datatable表</param>
        /// <returns>返回list集合</returns>

        public static  List<T> TableToList<T>(this DataTable dt) where T :class, new()
        {
            //定义集合
            List<T> list = new List<T>();
            //获得此模型的类型
            Type type = typeof(T);
            //定义一个临时变量
            string tempName = string.Empty;
            //遍历Datatable中所有的数据行
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();
                //获得此模型的公共属性
                PropertyInfo[] propertys = t.GetType().GetProperties();
                //遍历该对象的所有属性
                foreach (PropertyInfo pi in propertys)
                {
                    //将属性名称赋值给临时变量
                    tempName = pi.Name;
                    //检查DataTable是否包含此列（列名==对象的属性名）
                    if (dt.Columns.Contains(tempName))
                    {
                        //判断此属性是否有Setter
                        if (!pi.CanWrite) continue;//该属性不可写，直接跳出
                        //取值
                        object value = dr[tempName];
                        //如果非空，则赋给对象的属性
                        if (value != DBNull.Value)
                        {
                            //加一重if判断，如果属性值是int32类型的，就进行一次强制转换
                            Type typeColumn = pi.GetMethod.ReturnParameter.ParameterType;
                            if (typeColumn==typeof(Int32))
                            {
                                value = Convert.ToInt32(value);
                            }
                            else if (typeColumn == typeof(DateTime))
                            {
                                value = DateTime.Parse(value.ToString());
                            }
                            else if(typeColumn==typeof(DateTime?))
                            {                                
                                if (string.IsNullOrEmpty(value.ToString()))
                                {
                                    value = null;
                                }
                                else
                                {
                                    value = DateTime.Parse(value.ToString());
                                }
                                
                            }

                            pi.SetValue(t, value, null);
                            
                        }

                    }
                }
                //对象添加到泛型集合中
                list.Add(t);
            }
            return list;
        }
    }
}
